﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Configuration;

namespace ProcessoSeletivo.DAO
{
    public class GeraBoleto : IDisposable
    {
        #region " Listar "

        public DataView ListarBoleto()
        {
            DataSet lRetorno = new DataSet();
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("ListarBoleto", sql);
                sqlc.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter sqa = new SqlDataAdapter(sqlc);
                sqa.Fill(lRetorno, "tb_pse_boleto");
                sql.Close();
            }
            return lRetorno.Tables[0].DefaultView;
        }

        #endregion

        #region " Consultar "

        public DataView ConsultarBoleto(int cod_inscricao_ins)
        {
            DataSet lRetorno = new DataSet();
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("ConsultarBoleto", sql);
                sqlc.CommandType = CommandType.StoredProcedure;

                sqlc.Parameters.AddWithValue("@cod_inscricao_ins", cod_inscricao_ins);

                SqlDataAdapter sqa = new SqlDataAdapter(sqlc);
                sqa.Fill(lRetorno, "tb_pse_boleto");
                sql.Close();
            }
            return lRetorno.Tables[0].DefaultView;
        }

        #endregion

        #region " Incluir "

        public void IncluirBoleto(DataSet dsRegistro)
        {
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("IncluirBoleto", sql);
                sqlc.CommandType = CommandType.StoredProcedure;                
                sqlc.Parameters.AddWithValue("@cod_inscricao_ins", dsRegistro.Tables[0].Rows[0]["cod_inscricao_ins"]);
                sqlc.Parameters.AddWithValue("@val_boleto_bol", dsRegistro.Tables[0].Rows[0]["val_boleto_bol"]);
                sqlc.Parameters.AddWithValue("@num_nosso_numero_bol", dsRegistro.Tables[0].Rows[0]["num_nosso_numero_bol"]);
                sqlc.Parameters.AddWithValue("@dat_vencimento_bol", dsRegistro.Tables[0].Rows[0]["dat_vencimento_bol"]);
                sqlc.Parameters.AddWithValue("@flg_pago_bol", dsRegistro.Tables[0].Rows[0]["flg_pago_bol"]);
                sqlc.Parameters.AddWithValue("@des_login_usu", dsRegistro.Tables[0].Rows[0]["des_login_usu"]);

                sqlc.ExecuteNonQuery();
                sql.Close();
            }
        }

        #endregion

        #region " Alterar "

        public void AlterarBoleto(DataSet dsRegistro)
        {
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("AlterarBoleto", sql);
                sqlc.CommandType = CommandType.StoredProcedure;
                sqlc.Parameters.AddWithValue("@cod_inscricao_ins", dsRegistro.Tables[0].Rows[0]["cod_inscricao_ins"]);
                sqlc.Parameters.AddWithValue("@val_boleto_bol", dsRegistro.Tables[0].Rows[0]["val_boleto_bol"]);
                sqlc.Parameters.AddWithValue("@num_nosso_numero_bol", dsRegistro.Tables[0].Rows[0]["num_nosso_numero_bol"]);
                sqlc.Parameters.AddWithValue("@dat_vencimento_bol", dsRegistro.Tables[0].Rows[0]["dat_vencimento_bol"]);
                sqlc.Parameters.AddWithValue("@flg_pago_bol", dsRegistro.Tables[0].Rows[0]["flg_pago_bol"]);
                sqlc.Parameters.AddWithValue("@des_login_usu", dsRegistro.Tables[0].Rows[0]["des_login_usu"]);

                sqlc.ExecuteNonQuery();
                sql.Close();
            }
        }

        public void AlterarBoletoPago(int cod_inscricao_ins, bool flg_pago_bol)
        {
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("AlterarBoletoPago", sql);
                sqlc.CommandType = CommandType.StoredProcedure;
                sqlc.Parameters.AddWithValue("@cod_inscricao_ins", cod_inscricao_ins);
                sqlc.Parameters.AddWithValue("@flg_pago_bol", flg_pago_bol);

                sqlc.ExecuteNonQuery();
                sql.Close();
            }
        }

        #endregion

        #region " Excluir "

        public void ExcluirBoleto(int cod_inscricao_ins)
        {
            using (SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["StringConexao"].ToString()))
            {
                sql.Open();
                SqlCommand sqlc = new SqlCommand("ExcluirBoleto", sql);
                sqlc.CommandType = CommandType.StoredProcedure;
                sqlc.Parameters.AddWithValue("@cod_inscricao_ins", cod_inscricao_ins);
                sqlc.ExecuteNonQuery();

                sql.Close();
            }
        }
        
        #endregion

        #region " Dipose "

        public void Dispose()
        {
            GC.SuppressFinalize(this);
        }

        #endregion
    }
}
